#Region " Imports "

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Configuration
Imports System.Configuration.ConnectionStringsSection
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.Configuration
Imports System.Web.Security
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Globalization
Imports WebBase
Imports BA.DAL

#End Region

Public Class InstallerBase
    Inherits System.Web.UI.Page

    Private DAL As New DBUtility()

    Private ReadOnly Property Connection() As String
        Get
            If String.IsNullOrEmpty(ConfigurationManager.ConnectionStrings("BlazeApps").ToString()) Then
                Throw New ConfigurationErrorsException("The connection string is missing from the web config file.  Add a valid connection string to the connectionStrings section.")
            Else
                Return ConfigurationManager.ConnectionStrings("BlazeApps").ToString().Trim()
            End If
        End Get
    End Property

    Protected ReadOnly Property Provider() As DBUtility.Providers
        Get
            If String.IsNullOrEmpty(ConfigurationManager.ConnectionStrings("BlazeApps").ProviderName.ToString) Then
                Return CType(System.Enum.Parse(GetType(DBUtility.Providers), ConfigurationManager.AppSettings("DBProvider").ToString().Trim(), True), DBUtility.Providers)
            Else
                Return CType(System.Enum.Parse(GetType(DBUtility.Providers), ConfigurationManager.ConnectionStrings("BlazeApps").ProviderName.ToString().Trim(), True), DBUtility.Providers)
            End If
        End Get
    End Property

    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit

        Page.Theme = WebBase.AppTheme

    End Sub

    Protected Function ConnectionSuccess() As Boolean
        DAL.CreateDBObjects(Connection, Provider)

        Return DAL.CheckConnection(ConnectionState.Open)

    End Function

    Protected Sub DisplayMessage(ByVal Message As String, ByVal Valid As Boolean, ByVal Ctl As Label)

        If Valid Then
            Ctl.Visible = True
            Ctl.Attributes.Add("style", "background-color: Green; color:White; font-size: small; font-weight:bold; width: 98%;")
            Ctl.Text = String.Format(Message)
        Else
            Ctl.Visible = True
            Ctl.Attributes.Add("style", "background-color: Red; color:White; font-size: small; font-weight:bold; width: 98%;")
            Ctl.Text = String.Format(Message)
        End If

    End Sub

    Protected Sub LoadSettings(ByVal Ctl As TextBox)
        Ctl.Text = Connection.Trim
    End Sub

    Protected Function ExecuteDatabaseScripts(ByVal Scripts As String()) As Boolean
        Try

            If Provider = DBUtility.Providers.SqlServer Then
                GrantObjectAccess(False)
            End If

            For Each script As String In Scripts
                ExecuteScript(script)
            Next

            If Provider = DBUtility.Providers.SqlServer Then
                GrantObjectAccess(True)
            End If

        Catch x As Exception
            Throw New Exception(String.Format("Installing the database failed." & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & " The error message was:" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & "{0}", x))
        End Try
        Return True
    End Function

    Protected Sub ExecuteScript(ByVal sScriptFile As String)
        Dim statements As String() = Nothing
        Dim file As System.IO.StreamReader = Nothing
        Dim sScript As String = Nothing
        Try
            If Provider = DBUtility.Providers.SqlServer Then
                file = New System.IO.StreamReader(Request.MapPath(String.Format("DataBaseScripts/{0}/{1}", "MSSQL", sScriptFile)))
            End If

            If Provider = DBUtility.Providers.MySQL Then
                file = New System.IO.StreamReader(Request.MapPath(String.Format("DataBaseScripts/{0}/{1}", "MySQL", sScriptFile)))
            End If

            Try
                sScript = file.ReadToEnd + "" & Microsoft.VisualBasic.Chr(13) & "" & Microsoft.VisualBasic.Chr(10) & ""
                file.Close()
            Finally
                CType(file, IDisposable).Dispose()
            End Try
        Catch generatedExceptionVariable0 As System.IO.FileNotFoundException
            Return
        Catch x As Exception
            Throw New Exception("Failed to read " + sScriptFile, x)
        End Try

        If Provider = DBUtility.Providers.SqlServer Then
            statements = System.Text.RegularExpressions.Regex.Split(sScript, "\sGO\s", System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        End If

        If Provider = DBUtility.Providers.MySQL Then
            statements = System.Text.RegularExpressions.Regex.Split(sScript, "\s--GO\s", System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        End If

        For Each sql0 As String In statements
            Dim sql As String = sql0.Trim
            Try
                If sql.Length > 0 Then

                    DAL.CreateDBObjects(Connection, Provider)
                    DAL.ExecuteNonQuery(sql.Trim, CommandType.Text, ConnectionState.Open)
                End If
            Catch x As Exception
                Throw New Exception(String.Format("FILE:" & Microsoft.VisualBasic.Chr(10) & "{0}" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & "ERROR:" & Microsoft.VisualBasic.Chr(10) & "{2}" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(10) & "STATEMENT:" & Microsoft.VisualBasic.Chr(10) & "{1}", sScriptFile, sql, x.Message))
            End Try
        Next
    End Sub

    Protected Sub GrantObjectAccess(ByVal bGrant As Boolean)

        Dim conn As SqlConnection = New SqlConnection(Connection)

        conn.Open()

        Dim trans As SqlTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
        Dim da As SqlDataAdapter = New SqlDataAdapter("select Name,IsUserTable = OBJECTPROPERTY(id, N'IsUserTable'),IsScalarFunction = OBJECTPROPERTY(id, N'IsScalarFunction'),IsProcedure = OBJECTPROPERTY(id, N'IsProcedure'),IsView = OBJECTPROPERTY(id, N'IsView') from dbo.sysobjects", conn)
        da.SelectCommand.Transaction = trans
        Dim dt As DataTable = New DataTable("sysobjects")
        da.Fill(dt)
        Dim cmd As SqlCommand = conn.CreateCommand

        Try
            cmd.Transaction = trans
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "select current_user"
            Dim userName As String = CType(cmd.ExecuteScalar, String)
            If bGrant Then
                cmd.CommandType = CommandType.Text
                For Each row As DataRow In dt.Rows
                    If row("IsProcedure").ToString.Equals("1") OrElse row("IsScalarFunction").ToString.Equals("1") Then
                        cmd.CommandText = String.Format("grant execute on ""{0}"" to ""{1}""", row("Name"), userName)
                        cmd.ExecuteNonQuery()
                    End If
                    If row("IsUserTable").ToString.Equals("1") OrElse row("IsView").ToString.Equals("1") Then
                        cmd.CommandText = String.Format("grant select,update on ""{0}"" to ""{1}""", row("Name"), userName)
                        cmd.ExecuteNonQuery()
                    End If
                Next
            Else
                cmd.CommandText = "sp_changeobjectowner"
                cmd.CommandType = CommandType.StoredProcedure
                For Each row As DataRow In dt.Select("IsUserTable=1")
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@objname", row("Name"))
                    cmd.Parameters.AddWithValue("@newowner", "dbo")
                    Try
                        cmd.ExecuteNonQuery()
                    Catch generatedExceptionVariable0 As SqlException
                    End Try
                Next
                For Each row As DataRow In dt.Select("IsView=1")
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@objname", row("Name"))
                    cmd.Parameters.AddWithValue("@newowner", "dbo")
                    Try
                        cmd.ExecuteNonQuery()
                    Catch generatedExceptionVariable0 As SqlException
                    End Try
                Next
            End If
        Catch
            trans.Rollback()
        Finally
            trans.Commit()
            CType(cmd, IDisposable).Dispose()
            CType(dt, IDisposable).Dispose()
            CType(da, IDisposable).Dispose()
            CType(trans, IDisposable).Dispose()
            conn.Close()
            CType(conn, IDisposable).Dispose()
        End Try

    End Sub

    Protected Function IsDBCurrent(ByVal DBVersion As Decimal) As Boolean
        Dim IsCurrent As Boolean = False
        Dim DBValue As Decimal = 0

        Try
            DAL.CreateDBObjects(Connection, Provider)
            DBValue = Convert.ToDecimal(DAL.ExecuteScaler(String.Format("select version from dbversion where version = {0}", DBVersion), CommandType.Text, ConnectionState.Open).ToString)

            If DBValue = DBVersion Then
                IsCurrent = True
            Else
                IsCurrent = False
            End If

        Catch x As Exception
            IsCurrent = False
        End Try

        Return IsCurrent
    End Function

    Protected Function IsDBBuilt() As Boolean
        Dim IsBuilt As Boolean = False
        Dim DT As New DataTable

        Try
            DAL.CreateDBObjects(Connection, Provider)
            DT = DAL.GetDataTable(String.Format("select version from dbversion"), CommandType.Text, ConnectionState.Open)

            If DT.Rows.Count > 0 Then
                IsBuilt = True
            Else
                IsBuilt = False
            End If

        Catch x As Exception
            IsBuilt = False
        End Try

        Return IsBuilt
    End Function

End Class

